CREATE  VIEW [dbo].[vFullAddressAllReport]
AS
/****************************************************************************************
** This view provides all Full Address information for each Contact on "One Row per Contact" basis.
** This can be joined to other relevant tables or views to get the necessary address related information..
**
***************************************************************************************/
SELECT    
     AM1.FormattedAddress AS PhysicalAddress, AM2.FormattedAddress AS DayTimePhone,  AM3.FormattedAddress AS EveningPhone,
    AM4.FormattedAddress AS Fax, AM5.FormattedAddress AS Email, FA.FullAddressDesc AS FullAddressPurpose, PA.Address1, PA.Address2, PA.Address3,
    PA.City, PA.StateProvinceCode, PA.Region, PA.PostalCode, PA.CountryCode, PA.County, PA.MailCode, PA.CarrierRoute, PA.DeliveryPointBarCode, PA.LineOfTravel,
    PA.BarCode, PA.USCongress, PA.StateSenate, PA.Latitude, PA.Longitude, SPR.StateProvinceDesc, CR.CountryName,
  CRR.CommunicationReasonDesc, CRR.IsDefaultType, FA.FullAddressKey, FA.ContactKey,
    FA.SalutationKey, ATU.SeasonalRevertFullAddressKey, CCRP.PreferredAddressCategoryCode,
  ACR.AddressCategoryDesc AS PreferredAddressCategory

FROM         
    FullAddress     FA
    LEFT OUTER JOIN     PhysicalAddress     PA     ON     FA.PhysicalAddressKey = PA.AddressKey
    LEFT OUTER JOIN     AddressMain         AM1     ON     FA.PhysicalAddressKey = AM1.AddressKey  
    LEFT OUTER JOIN     AddressMain         AM2     ON     FA.DaytimePhoneAddressKey = AM2.AddressKey
    LEFT OUTER JOIN     AddressMain         AM3     ON     FA.EveningPhoneAddressKey = AM3.AddressKey
    LEFT OUTER JOIN     AddressMain         AM4     ON     FA.FaxAddressKey = AM4.AddressKey
    LEFT OUTER JOIN     AddressMain         AM5     ON     FA.EmailAddressKey = AM5.AddressKey
    LEFT OUTER JOIN     AddressToUse         ATU     ON     FA.FullAddressKey = ATU.FullAddressKey
    LEFT OUTER JOIN     StateProvinceRef     SPR     ON     PA.StateProvinceCode = SPR.StateProvinceCode
    LEFT OUTER JOIN     CountryRef         CR     ON     PA.CountryCode = CR.CountryCode
    LEFT OUTER JOIN     CommunicationReasonRef CRR     ON     ATU.CommunicationReasonKey = CRR.CommunicationReasonKey
      LEFT OUTER JOIN   ContactCommunicationReasonPreferences CCRP ON ATU.CommunicationReasonKey = CCRP.CommunicationReasonKey
                    AND ATU.ContactKey = CCRP.ContactKey
      LEFT OUTER JOIN   AddressCategoryRef ACR ON CCRP.PreferredAddressCategoryCode = ACR.AddressCategoryCode

GO